
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

public class OldMACD {
    public static void main(String[] args)
    {
        try
        {
            FileInputStream file = new FileInputStream(new File("D:/tcsbo.xls"));
            HSSFWorkbook workbook = new HSSFWorkbook(file);
            HSSFSheet sheet = workbook.getSheetAt(0);
            Cell cell = null;

            sheet.getRow(0).createCell(7).setCellValue("SMA 12 day");
            sheet.getRow(0).createCell(8).setCellValue("EMA 12 day");
            sheet.getRow(0).createCell(9).setCellValue("SMA 26 day");
            sheet.getRow(0).createCell(10).setCellValue("EMA 26 day");
            sheet.getRow(0).createCell(11).setCellValue("MACD line");
            
            sheet.getRow(0).createCell(12).setCellValue("Signal SMA");
            sheet.getRow(0).createCell(13).setCellValue("Signal EMA");
            sheet.getRow(0).createCell(14).setCellValue("Histogram");
//for SMA 
            for(int i=13;i<sheet.getLastRowNum();i++)
            {
                sheet.getRow(i-1).createCell(7).setCellFormula("AVERAGE(E"+(i-11)+":E"+(i)+")");
            }
            
            for(int i=27;i<sheet.getLastRowNum();i++)
            {
                sheet.getRow(i-1).createCell(9).setCellFormula("AVERAGE(E"+(i-25)+":E"+(i)+")");
            }

//for EMA 
            sheet.getRow(12).createCell(8).setCellFormula("H13");
            sheet.getRow(26).createCell(10).setCellFormula("J27");
            
            for(int i=14;i<sheet.getLastRowNum();i++)
            {
                sheet.getRow(i-1).createCell(8).setCellFormula("0.1538*(E"+(i)+"-I"+(i-1)+")+I"+(i-1));
            }
            
            for(int i=28;i<sheet.getLastRowNum();i++)
            {
                sheet.getRow(i-1).createCell(10).setCellFormula("0.07407*(E"+(i)+"-K"+(i-1)+")+K"+(i-1));
            }
            
// for MACD line
            for(int i=27;i<sheet.getLastRowNum();i++)
            {
                sheet.getRow(i-1).createCell(11).setCellFormula("I"+(i)+"-K"+(i));
            }
            
//for signal
            for(int i=35;i<sheet.getLastRowNum();i++)
            {
                sheet.getRow(i-1).createCell(12).setCellFormula("AVERAGE(L"+(i-8)+":L"+(i)+")");
            }
            
            sheet.getRow(34).createCell(13).setCellFormula("M35");
            
            for(int i=36;i<sheet.getLastRowNum();i++)
            {
                sheet.getRow(i-1).createCell(13).setCellFormula("0.2*(E"+(i)+"-N"+(i-1)+")+N"+(i-1));
            }

//for histogram
            
            for(int i=35;i<sheet.getLastRowNum();i++)
            {
                sheet.getRow(i-1).createCell(14).setCellFormula("L"+(i)+"-N"+(i));
            }
            
            file.close();
			
            FileOutputStream outFile =new FileOutputStream(new File("D:/tcsbo.xls"));
            workbook.write(outFile);
            outFile.close();
        }
        catch (FileNotFoundException e) {e.printStackTrace();} 
        catch (IOException e) {e.printStackTrace();}
    }
}
